﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
namespace QL_DTTN.DAL
{
   public class SqlDataProvider
    {
       private string  _StrConSQL;

        private SqlConnection _sqlConn;
        private SqlCommand _sqlComm;
        public SqlDataProvider()
        {
     
            _StrConSQL = ClsFunctions.strConnecSQL;
            _sqlConn= new SqlConnection(_StrConSQL);
            _sqlComm = _sqlConn.CreateCommand();
        }
        public SqlDataProvider(string strConSQL)
        {
            _StrConSQL = strConSQL;
            _sqlConn = new SqlConnection(_StrConSQL);
            _sqlComm = _sqlConn.CreateCommand();
        }
        public bool OpenConnection()
        {
            try
            {
                if (_sqlConn.State != ConnectionState.Open) _sqlConn.Open();
                return true;
            }
            catch
            {
                _sqlConn.Dispose();
                _sqlComm.Dispose();
                return false;
            }
        }
        public void CloseConnection()
        {
            try
            {
                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlComm.Parameters.Clear();
                    _sqlComm.Dispose();
                    _sqlConn.Dispose();

                }
                else
                {
                    _sqlComm.Parameters.Clear();
                    _sqlComm.Dispose();
                    _sqlConn.Dispose();
                }
            }
            catch
            {
                _sqlComm.Parameters.Clear();
                _sqlComm.Dispose();
                _sqlConn.Dispose();
            }
        }
        public void AddParameter(SqlParameter sqlParameter)
        {
            _sqlComm.Parameters.Add(sqlParameter);
        }
        //PhuongThuc
        public SqlDataReader ExDataReader(string strSelectSQL, bool sqlCommTypeText)
        {
            //try
            //{
                OpenConnection();
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }
                _sqlComm.CommandText = strSelectSQL;
                return _sqlComm.ExecuteReader();
            //}
            //catch
            //{
            //    CloseConnection();
            //    return null;
            //}
        }
        public DataTable ExDataTable(string strSelectSQL, bool sqlCommTypeText)
        {
            //try
            //{
                OpenConnection();
                SqlDataAdapter sqlAdap = new SqlDataAdapter();
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }
                _sqlComm.CommandText = strSelectSQL;
                sqlAdap.SelectCommand = _sqlComm;
                DataTable dt = new DataTable();
                sqlAdap.Fill(dt);
                CloseConnection();
                return dt;
            //}
            //catch
            //{
            //    CloseConnection();
            //    return new DataTable();
            //}
        }
        public bool RunReturnSQL(string sql, ref object ketQua, bool sqlCommTypeText)
        {
         
                if(!OpenConnection())return false;
                SqlTransaction objTransaction = _sqlConn.BeginTransaction("MyTrans");
                _sqlComm.Transaction = objTransaction;
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }
                _sqlComm.CommandText = sql;
           //try
           // { 
               ketQua = _sqlComm.ExecuteScalar();  
                if (ketQua == null)
                {
                    objTransaction.Rollback();
                    objTransaction.Dispose();
                    _sqlComm.Dispose();
                    CloseConnection();
                    return false;
                }
                else
                {
                    objTransaction.Commit();
                    objTransaction.Dispose();
                    CloseConnection();
                    return true;
                }
           // }
           // catch
           //{
           //    objTransaction.Rollback();
           //    objTransaction.Dispose();
           //    _sqlComm.Dispose();
           //    CloseConnection();
           //    return false;
           // }
        }

        public bool RunSQL(string sql, bool sqlCommTypeText)
        {
            //try
            //{
                if(!OpenConnection())return false;
                SqlTransaction objTransaction = _sqlConn.BeginTransaction("MyTrans");
                _sqlComm.Transaction = objTransaction;
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }
                _sqlComm.CommandText = sql;
                int n = 0;
                n = _sqlComm.ExecuteNonQuery();
                if (n == 0)
                {
                    objTransaction.Rollback();
                    objTransaction.Dispose();
                    _sqlComm.Dispose();
                    CloseConnection();
                    return false;
                }
                else
                {
                    objTransaction.Commit();
                    objTransaction.Dispose();
                    CloseConnection();
                    return true;
                }
            //}
            //catch 
            //{
            //    return false;
            //}
        }
        public bool RunSQL(string sql,ref int soDong, bool sqlCommTypeText)
        {
            try
            {
                if (!OpenConnection()) return false;
                SqlTransaction objTransaction = _sqlConn.BeginTransaction("MyTrans");
                _sqlComm.Transaction = objTransaction;
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }
                _sqlComm.CommandText = sql;
                soDong = 0;
                soDong = _sqlComm.ExecuteNonQuery();
                if (soDong == 0)
                {
                    objTransaction.Rollback();
                    objTransaction.Dispose();
                    _sqlComm.Dispose();
                    CloseConnection();
                    return false;
                }
                else
                {
                    objTransaction.Commit();
                    objTransaction.Dispose();
                    CloseConnection();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
        public bool runSQLListQuery(List<string> lstQuery, bool sqlCommTypeText)
        {
            //try
            //{
                if (!OpenConnection()) return false;
                SqlTransaction objTransaction = _sqlConn.BeginTransaction("MyTrans");
                _sqlComm.Transaction = objTransaction;
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }

                //try
                //{
                    foreach (var item in lstQuery)
                    {
                        int n = 0;
                        _sqlComm.CommandText = item;
                        n = _sqlComm.ExecuteNonQuery();
                        if (n == 0)
                        {
                            objTransaction.Rollback();
                            objTransaction.Dispose();
                            _sqlComm.Dispose();
                            CloseConnection();
                            return false;
                        }
                    }
                    objTransaction.Commit();
                    objTransaction.Dispose();
                    CloseConnection();
                    return true;
        //        }
        //        catch
        //        {
        //            objTransaction.Dispose();
        //            CloseConnection();
        //            return false;
        //        }
        //    }
        //    catch
        //    {
        //        return false;
        //    }
        }

        public bool RunAddNewIdentity(string strAddNewReturn, string strAddNew, string keyParameter, bool sqlCommTypeText)
        {
            try
            {
                if (!OpenConnection()) return false;
                SqlTransaction objTransaction = _sqlConn.BeginTransaction("MyTrans");
                _sqlComm.Transaction = objTransaction;
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }

                try
                {
                   
                        int n = 0;
                        _sqlComm.CommandText = strAddNewReturn;
                        object tam = _sqlComm.ExecuteScalar();
                        if (tam!=null)
                        {
                          
                            _sqlComm.CommandText = strAddNew; 
                            AddParameter(new SqlParameter(keyParameter, tam));
                            n = _sqlComm.ExecuteNonQuery(); 
                                if (n == 0)
                                {
                                    objTransaction.Rollback();
                                    objTransaction.Dispose();
                                    _sqlComm.Dispose();
                                    CloseConnection();
                                    return false;
                                }
                                else
                                { 
                                    objTransaction.Commit();
                                    objTransaction.Dispose();
                                    CloseConnection();
                                    return true;
                                }
                        }
                        else
                        {
                            objTransaction.Rollback();
                            objTransaction.Dispose();
                            _sqlComm.Dispose();
                            CloseConnection();
                            return false;
                        }
                   
                }
                catch
                {
                    objTransaction.Dispose();
                    CloseConnection();
                    return false;
                }
            }
            catch
            {
                CloseConnection();
                return false;
            }
        }
        public bool RunAddNewIdentity(string strAddNewReturn, List<string>lstQuery, string keyParameter, bool sqlCommTypeText)
        {
            //try
            //{
                if (!OpenConnection()) return false;
                SqlTransaction objTransaction = _sqlConn.BeginTransaction("MyTrans");
                _sqlComm.Transaction = objTransaction;
                if (sqlCommTypeText)
                {
                    _sqlComm.CommandType = CommandType.Text;
                }
                else
                {
                    _sqlComm.CommandType = CommandType.StoredProcedure;
                }

                //try
                //{

                    int n = 0;
                    _sqlComm.CommandText = strAddNewReturn;
                    object tam = _sqlComm.ExecuteScalar();
                    if (tam != null)
                    {
                        AddParameter(new SqlParameter(keyParameter, tam));
                        foreach (var item in lstQuery)
                        {
                            _sqlComm.CommandText = item;
                            n = _sqlComm.ExecuteNonQuery();
                            if (n == 0)
                            {
                                objTransaction.Rollback();
                                objTransaction.Dispose();
                                _sqlComm.Dispose();
                                CloseConnection();
                                return false;
                            }
                        }
                        objTransaction.Commit();
                        objTransaction.Dispose();
                        CloseConnection();
                        return true;
                    }
                    else
                    {
                        objTransaction.Rollback();
                        objTransaction.Dispose();
                        _sqlComm.Dispose();
                        CloseConnection();
                        return false;
                    }

            //    }
            //    catch
            //    {
            //        objTransaction.Dispose();
            //        CloseConnection();
            //        return false;
            //    }
            //}
            //catch
            //{
            //    CloseConnection();
            //    return false;
            //}
        }

       #region Stransaction 

       private SqlCommand[] _arrSqlCommand = null;
       private List<ArrayList> _lstSqlQueryString;
       private SqlTransaction _sqlTran;
       private int _indexCommand=-1;   
       private void commitTran()
       {    
               _indexCommand = -1;       
               _sqlTran.Commit();
               for (int i = _indexCommand; i < 0; i--)
               {
                   _arrSqlCommand[i].Parameters.Clear();
                   _arrSqlCommand[i].Dispose();
               }
               _sqlTran.Dispose();
               _lstSqlQueryString.Clear();
               _lstSqlQueryString = null ;
 
               CloseConnection();     
       }
       private void rollbackTran()
       {      
            _indexCommand = -1;
               for(int i=_indexCommand;i<0;i--)
               {  
                  _arrSqlCommand[i] .Parameters.Clear();
                  _arrSqlCommand[i].Dispose();
               }
               _sqlTran.Dispose();
               _sqlTran.Rollback();
               _lstSqlQueryString.Clear();
               _lstSqlQueryString = null;
          
               CloseConnection();    
       }     
       public bool StartTran()
       {
           _arrSqlCommand = new SqlCommand[20];
           if (!OpenConnection()) return false;
           try
           {
               _lstSqlQueryString = new List<ArrayList>();
               _indexCommand = -1;
               _sqlTran = _sqlConn.BeginTransaction();
               AddSqlCommand();
               return true;
           }
           catch
           {
               return false;

           }
       }
       public bool StartTran(int soCommand)
       {
           _arrSqlCommand = new SqlCommand[soCommand];
            if ( !OpenConnection() )return false;
               try
               {
                   _lstSqlQueryString = new List<ArrayList>();
                   _indexCommand = -1;
                   _sqlTran=_sqlConn.BeginTransaction();
                   AddSqlCommand();
                   return true;
	            }
	            catch 
	            {
		            return false;
	
	            }
       }
       public void AddSqlCommand()
       {
               SqlCommand sqlCom = _sqlConn.CreateCommand();
               sqlCom.Transaction = _sqlTran;        
               _arrSqlCommand[_indexCommand]=sqlCom;
                _indexCommand += 1;
       }
       public void AddParOfCommandIndex(SqlParameter sqlParameter)
       {
           if (_indexCommand == -1) return;
           _arrSqlCommand[_indexCommand].Parameters.Add(sqlParameter);
       }
       public void AddQueryOfCommandIndex(string strSql, bool sqlCommTypeText)
       {
           if (_indexCommand == -1) return;
              ArrayList arr = new ArrayList();
              arr.Add(_indexCommand);
              arr.Add(sqlCommTypeText);
              arr.Add(strSql);
              _lstSqlQueryString.Add(arr);
       }
       public void AddQueryOfCommandIndex(List<string> lstStrSql, bool sqlCommTypeText)
       {
           if (_indexCommand == -1) return;
           foreach (var item in lstStrSql)
           {
               ArrayList arr = new ArrayList();
               arr.Add(_indexCommand);
               arr.Add(sqlCommTypeText);
               arr.Add(item);
               _lstSqlQueryString.Add(arr);
           }
           
       }
       public bool RunSqlTran()
       {
           if (_indexCommand == -1) return false;
           try
           {
               foreach (var item in _lstSqlQueryString)
               {
                   if (Convert.ToBoolean(item[1]))
                   {
                       _arrSqlCommand[Convert.ToInt32(item[0])].CommandType = CommandType.Text;
                   }
                   else
                   {
                       _arrSqlCommand[Convert.ToInt32(item[0])].CommandType = CommandType.StoredProcedure;
                   }
                   _arrSqlCommand[Convert.ToInt32(item[0])].CommandText = item[2].ToString();
                   int i = 0;
                   i = _arrSqlCommand[Convert.ToInt32(item[0])].ExecuteNonQuery();
                   if (i <= 0)
                   {
                       rollbackTran();
                       return false;
                   }
               }
               commitTran();
               return true;
           }
           catch
           {
               rollbackTran();
               return false;
           }
       }
        #endregion

    }
}
